import 'package:awak/common/values/images.dart';
import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';

class DBHelper {
  static Database? _db;

  static Future<Database> get database async {
    if (_db != null) return _db!;
    _db = await _initDB();
    return _db!;
  }

  static Future<Database> _initDB() async {
    String path = join(await getDatabasesPath(), 'awak.db');
    return await openDatabase(
      path,
      version: 11, // 增加版本号以支持today_goal表
      onCreate: (db, version) async {
        // 创建用户表
        await db.execute('''
          CREATE TABLE user (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT UNIQUE,
            password TEXT,
            nickname TEXT,
            avatar TEXT,
            birthday TEXT,
            signature TEXT,
            city TEXT
          )
        ''');

        // 创建房间表
        await db.execute('''
          CREATE TABLE room (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            roomName TEXT,
            viewPoint TEXT,
            roomAvatar TEXT
          )
        ''');

        // 创建评论表
        await db.execute('''
          CREATE TABLE comment (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            content TEXT NOT NULL,
            userId INTEGER NOT NULL,
            roomId INTEGER NOT NULL,
            FOREIGN KEY (userId) REFERENCES user (id),
            FOREIGN KEY (roomId) REFERENCES room (id)
          )
        ''');

        // 创建主题表
        await db.execute('''
          CREATE TABLE topic (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            coverImage TEXT NOT NULL,
            content TEXT NOT NULL,
            label TEXT NOT NULL,
            numberPeople INTEGER NOT NULL,
            userId INTEGER NOT NULL,
            FOREIGN KEY (userId) REFERENCES user (id)
          )
        ''');

        // 创建内容表
        await db.execute('''
          CREATE TABLE content (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            picture TEXT NOT NULL,
            description TEXT NOT NULL,
            userId INTEGER NOT NULL,
            topicId INTEGER NOT NULL,
            publishedAt INTEGER,
            FOREIGN KEY (userId) REFERENCES user (id),
            FOREIGN KEY (topicId) REFERENCES topic (id)
          )
        ''');

        // 创建内容评论表
        await db.execute('''
          CREATE TABLE review (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            content TEXT NOT NULL,
            userId INTEGER NOT NULL,
            contentId INTEGER NOT NULL,
            createdAt INTEGER,
            FOREIGN KEY (userId) REFERENCES user (id),
            FOREIGN KEY (contentId) REFERENCES content (id)
          )
        ''');

        // 创建收藏表
        await db.execute('''
          CREATE TABLE favorite (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            userId INTEGER NOT NULL,
            contentId INTEGER NOT NULL,
            createdAt INTEGER,
            FOREIGN KEY (userId) REFERENCES user (id),
            FOREIGN KEY (contentId) REFERENCES content (id),
            UNIQUE(userId, contentId)
          )
        ''');

        // 创建聊天表
        await db.execute('''
          CREATE TABLE chat (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            sendUserId INTEGER NOT NULL,
            acceptUserId INTEGER NOT NULL,
            context TEXT NOT NULL,
            sendTime INTEGER NOT NULL,
            isRead INTEGER DEFAULT 0,
            FOREIGN KEY (sendUserId) REFERENCES user (id),
            FOREIGN KEY (acceptUserId) REFERENCES user (id)
          )
        ''');

        // 创建好友关系表
        await db.execute('''
          CREATE TABLE friendship (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            userId INTEGER NOT NULL,
            friendId INTEGER NOT NULL,
            createdAt INTEGER NOT NULL,
            FOREIGN KEY (userId) REFERENCES user (id),
            FOREIGN KEY (friendId) REFERENCES user (id),
            UNIQUE(userId, friendId)
          )
        ''');

        // 创建计划表
        await db.execute('''
          CREATE TABLE plan (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            description TEXT DEFAULT '',
            picture TEXT NOT NULL,
            startTime INTEGER NOT NULL,
            endTime INTEGER NOT NULL,
            state TEXT NOT NULL,
            userId INTEGER NOT NULL,
            FOREIGN KEY (userId) REFERENCES user (id)
          )
        ''');

        // 创建今日目标表
        await db.execute('''
          CREATE TABLE today_goal (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            userId INTEGER NOT NULL,
            FOREIGN KEY (userId) REFERENCES user (id)
          )
        ''');

        // 插入默认用户
        await db.insert('user', {
          'username': 'user1',
          'password': '123456',
          'nickname': 'Alpha',
          'avatar': AwakImages.avatar1,
          'birthday': '2000-01-01',
          'signature': 'Keep moving',
          'city': 'New York',
        });
        await db.insert('user', {
          'username': 'user2',
          'password': '123456',
          'nickname': 'Bravo',
          'avatar': AwakImages.avatar2,
          'birthday': '1999-05-12',
          'signature': 'Dream big',
          'city': 'London',
        });
        await db.insert('user', {
          'username': 'user3',
          'password': '123456',
          'nickname': 'Charlie',
          'avatar': AwakImages.avatar3,
          'birthday': '2001-09-23',
          'signature': 'Stay strong',
          'city': 'Sydney',
        });
        await db.insert('user', {
          'username': 'user4',
          'password': '123456',
          'nickname': 'Delta',
          'avatar': AwakImages.avatar4,
          'birthday': '1998-12-31',
          'signature': 'Never give up',
          'city': 'Toronto',
        });
        await db.insert('user', {
          'username': 'user5',
          'password': '123456',
          'nickname': 'Echo',
          'avatar': AwakImages.avatar,
          'birthday': '2002-03-15',
          'signature': 'Believe in yourself',
          'city': 'Tokyo',
        });
        // 插入 Learning Exchange 房间 (ID: 1-4)
        await db.insert('room', {
          'roomName': 'Chatting About Learning Methods',
          'viewPoint': 'My Own Years Of Learning Experience Tell Me That Only By Working Hard Can One Find A Way Out.',
          'roomAvatar': AwakImages.learning1,
        });
        await db.insert('room', {
          'roomName': 'Discussion on Thinking Patterns',
          'viewPoint': 'Code is poetry, and every bug is a lesson learned.',
          'roomAvatar': AwakImages.learning2,
        });
        await db.insert('room', {
          'roomName': 'Sharing of reading books',
          'viewPoint': 'Life is not about finding yourself, it\'s about creating yourself.',
          'roomAvatar': AwakImages.learning3,
        });
        await db.insert('room', {
          'roomName': 'Illustration Design Exchange',
          'viewPoint': 'Success is not final, failure is not fatal: it is the courage to continue that counts.',
          'roomAvatar': AwakImages.learning4,
        });

        // 插入 Room Recommendations 房间 (ID: 5-8)
        await db.insert('room', {
          'roomName': 'Photography Exchange',
          'viewPoint': 'Share your photography skills and learn from others in this creative community.',
          'roomAvatar': AwakImages.room1,
        });
        await db.insert('room', {
          'roomName': 'User Interface Chat',
          'viewPoint': 'Discuss UI design trends, share your work, and get feedback from fellow designers.',
          'roomAvatar': AwakImages.room2,
        });
        await db.insert('room', {
          'roomName': 'Tourism Exchange',
          'viewPoint': 'Share travel experiences, discover new destinations, and connect with fellow travelers.',
          'roomAvatar': AwakImages.room3,
        });
        await db.insert('room', {
          'roomName': 'Design Exercise',
          'viewPoint': 'Practice design skills through challenges and exercises with the community.',
          'roomAvatar': AwakImages.room4,
        });

        // 插入默认评论数据

        // Learning Exchange 房间的评论 (roomId 1-4)
        // 房间1: Chatting About Learning Methods
        await db.insert('comment', {
          'content': 'Great discussion topic! I totally agree with this viewpoint.',
          'userId': 2,
          'roomId': 1,
        });
        await db.insert('comment', {
          'content': 'This reminds me of my own learning journey. Hard work really pays off.',
          'userId': 3,
          'roomId': 1,
        });
        await db.insert('comment', {
          'content': 'Thanks for sharing this wisdom. Very inspiring!',
          'userId': 4,
          'roomId': 1,
        });

        // 房间2: Programming Discussion
        await db.insert('comment', {
          'content': 'I love discussing programming concepts with fellow developers.',
          'userId': 1,
          'roomId': 2,
        });
        await db.insert('comment', {
          'content': 'Code reviews have taught me so much about writing better code.',
          'userId': 3,
          'roomId': 2,
        });

        // 房间3: Life Philosophy
        await db.insert('comment', {
          'content': 'Philosophy helps us understand the deeper meaning of life.',
          'userId': 2,
          'roomId': 3,
        });

        // 房间4: Career Development
        await db.insert('comment', {
          'content': 'Career growth requires continuous learning and adaptation.',
          'userId': 1,
          'roomId': 4,
        });

        // Room Recommendations 房间的评论 (roomId 5-8)
        // 房间5: Photography Exchange
        await db.insert('comment', {
          'content': 'Amazing photography tips! Love the composition techniques.',
          'userId': 1,
          'roomId': 5,
        });
        await db.insert('comment', {
          'content': 'The lighting in your photos is incredible!',
          'userId': 2,
          'roomId': 5,
        });
        await db.insert('comment', {
          'content': 'Can you share more about your camera settings?',
          'userId': 3,
          'roomId': 5,
        });
        await db.insert('comment', {
          'content': 'This community has really improved my photography skills.',
          'userId': 4,
          'roomId': 5,
        });
        await db.insert('comment', {
          'content': 'Great feedback from everyone here!',
          'userId': 5,
          'roomId': 5,
        });

        // 房间6: UI Communication
        await db.insert('comment', {
          'content': 'Love the clean design approach discussed here.',
          'userId': 2,
          'roomId': 6,
        });
        await db.insert('comment', {
          'content': 'The color palette suggestions are very helpful.',
          'userId': 3,
          'roomId': 6,
        });
        await db.insert('comment', {
          'content': 'UI trends are evolving so fast, great to stay updated.',
          'userId': 4,
          'roomId': 6,
        });
        await db.insert('comment', {
          'content': 'Figma tips shared here are amazing!',
          'userId': 1,
          'roomId': 6,
        });
        await db.insert('comment', {
          'content': 'Typography discussions are always insightful.',
          'userId': 5,
          'roomId': 6,
        });
        await db.insert('comment', {
          'content': 'User experience feedback is invaluable.',
          'userId': 1,
          'roomId': 6,
        });
        await db.insert('comment', {
          'content': 'Design systems discussion was very helpful.',
          'userId': 2,
          'roomId': 6,
        });

        // 房间7: Tourism Exchange
        await db.insert('comment', {
          'content': 'Your travel photos are breathtaking!',
          'userId': 1,
          'roomId': 7,
        });
        await db.insert('comment', {
          'content': 'Thanks for the budget travel tips.',
          'userId': 3,
          'roomId': 7,
        });
        await db.insert('comment', {
          'content': 'That hidden gem you shared was amazing.',
          'userId': 4,
          'roomId': 7,
        });

        // 房间8: Design Exercise
        await db.insert('comment', {
          'content': 'The design challenge was really fun!',
          'userId': 2,
          'roomId': 8,
        });
        await db.insert('comment', {
          'content': 'Learning so much from these exercises.',
          'userId': 3,
          'roomId': 8,
        });
        await db.insert('comment', {
          'content': 'Great way to practice design skills.',
          'userId': 1,
          'roomId': 8,
        });
        await db.insert('comment', {
          'content': 'The feedback from the community is excellent.',
          'userId': 4,
          'roomId': 8,
        });
        await db.insert('comment', {
          'content': 'These challenges push my creativity.',
          'userId': 5,
          'roomId': 8,
        });
        await db.insert('comment', {
          'content': 'Love the variety of design problems.',
          'userId': 1,
          'roomId': 8,
        });
        await db.insert('comment', {
          'content': 'The solutions shared are very inspiring.',
          'userId': 2,
          'roomId': 8,
        });
        await db.insert('comment', {
          'content': 'Perfect for improving design thinking.',
          'userId': 3,
          'roomId': 8,
        });
        await db.insert('comment', {
          'content': 'Great community for designers!',
          'userId': 4,
          'roomId': 8,
        });

        // 插入默认主题数据
        await db.insert('topic', {
          'title': 'Philosophical Thinking Topic Arrivals!',
          'coverImage': AwakImages.topic1,
          'content': '23',
          'label': 'Philosophical',
          'numberPeople': 98,
          'userId': 1,
        });
        await db.insert('topic', {
          'title': 'Travel Adventures Await!',
          'coverImage': AwakImages.topic2,
          'content': '15',
          'label': 'traveling',
          'numberPeople': 67,
          'userId': 2,
        });
        await db.insert('topic', {
          'title': 'Let\'s Think Carefully!',
          'coverImage': AwakImages.topic3,
          'content': '8',
          'label': 'psychological learning',
          'numberPeople': 42,
          'userId': 3,
        });
        await db.insert('topic', {
          'title': 'Early To Bed, Early To Rise!',
          'coverImage': AwakImages.topic4,
          'content': '31',
          'label': 'getting up early',
          'numberPeople': 156,
          'userId': 4,
        });

        // 插入内容数据
        final baseTime = DateTime.now().millisecondsSinceEpoch;

        // 哲学主题的内容 (topicId: 1) - 按时间倒序发布
        await db.insert('content', {
          'picture': AwakImages.philosophical1,
          'description': 'Philosophy is the study of the nature of reality, existence, knowledge, values, reason, mind, and language. It is the search for wisdom and understanding of the world and our place in it.',
          'userId': 1,
          'topicId': 1,
          'publishedAt': baseTime - 86400000 * 10, // 10天前
        });
        await db.insert('content', {
          'picture': AwakImages.philosophical2,
          'description': 'The unexamined life is not worth living. This famous quote from Socrates reminds us of the importance of self-reflection and critical thinking in our daily lives.',
          'userId': 2,
          'topicId': 1,
          'publishedAt': baseTime - 86400000 * 8, // 8天前
        });
        await db.insert('content', {
          'picture': AwakImages.philosophical3,
          'description': 'Existentialism teaches us that we are responsible for creating our own meaning in life. We must embrace our freedom and make authentic choices.',
          'userId': 3,
          'topicId': 1,
          'publishedAt': baseTime - 86400000 * 6, // 6天前
        });

        // 旅行主题的内容 (topicId: 2)
        await db.insert('content', {
          'picture': AwakImages.travel1,
          'description': 'Exploring new cultures and meeting different people broadens our perspective and enriches our understanding of the world.',
          'userId': 2,
          'topicId': 2,
          'publishedAt': baseTime - 86400000 * 4, // 4天前
        });
        await db.insert('content', {
          'picture': AwakImages.travel2,
          'description': 'Adventure awaits those who dare to step out of their comfort zone. Every journey teaches us something new about ourselves.',
          'userId': 4,
          'topicId': 2,
          'publishedAt': baseTime - 86400000 * 2, // 2天前
        });

        // 心理学习主题的内容 (topicId: 3)
        await db.insert('content', {
          'picture': AwakImages.psychologicalLearning1,
          'description': 'Understanding our emotions and thought patterns is the first step towards personal growth and mental well-being.',
          'userId': 3,
          'topicId': 3,
          'publishedAt': baseTime - 86400000 * 5, // 5天前
        });
        await db.insert('content', {
          'picture': AwakImages.psychologicalLearning2,
          'description': 'Mindfulness and meditation can help us develop better emotional regulation and reduce stress in our daily lives.',
          'userId': 5,
          'topicId': 3,
          'publishedAt': baseTime - 86400000 * 3, // 3天前
        });
        await db.insert('content', {
          'picture': AwakImages.psychologicalLearning3,
          'description': 'Cognitive behavioral therapy techniques can be powerful tools for changing negative thought patterns and improving mental health.',
          'userId': 1,
          'topicId': 3,
          'publishedAt': baseTime - 86400000 * 1, // 1天前
        });

        // 早起主题的内容 (topicId: 4)
        await db.insert('content', {
          'picture': AwakImages.earlyRising1,
          'description': 'Early to bed and early to rise makes a man healthy, wealthy, and wise. This timeless wisdom still holds true today.',
          'userId': 4,
          'topicId': 4,
          'publishedAt': baseTime - 86400000 * 9, // 9天前
        });
        await db.insert('content', {
          'picture': AwakImages.earlyRising2,
          'description': 'Morning routines set the tone for the entire day. Starting early gives us more time to accomplish our goals.',
          'userId': 1,
          'topicId': 4,
          'publishedAt': baseTime - 86400000 * 7, // 7天前
        });
        await db.insert('content', {
          'picture': AwakImages.earlyRising3,
          'description': 'The quiet hours of the morning provide the perfect opportunity for reflection, planning, and personal development.',
          'userId': 2,
          'topicId': 4,
          'publishedAt': baseTime - 3600000 * 12, // 12小时前 (最新)
        });
        await db.insert('content', {
          'picture': AwakImages.earlyRising1, // 使用已有的图片资源
          'description': 'Consistency in sleep schedule improves both physical health and mental clarity. Your body will thank you for the routine.',
          'userId': 5,
          'topicId': 4,
          'publishedAt': baseTime - 3600000 * 6, // 6小时前 (最新)
        });

        // 插入评论数据
        final now = DateTime.now().millisecondsSinceEpoch;

        // 哲学主题内容的评论 (contentId: 1)
        await db.insert('review', {
          'content': 'This is such a profound perspective on philosophy! Really makes me think about the nature of existence.',
          'userId': 2,
          'contentId': 1,
          'createdAt': now - 3600000, // 1小时前
        });
        await db.insert('review', {
          'content': 'I love how you explained the complexity of philosophical thinking. Very insightful!',
          'userId': 3,
          'contentId': 1,
          'createdAt': now - 1800000, // 30分钟前
        });
        await db.insert('review', {
          'content': 'Philosophy has always fascinated me. Thanks for sharing this perspective!',
          'userId': 4,
          'contentId': 1,
          'createdAt': now - 900000, // 15分钟前
        });

        // 哲学主题内容的评论 (contentId: 2)
        await db.insert('review', {
          'content': 'Socrates was truly ahead of his time. The unexamined life quote is so relevant today.',
          'userId': 1,
          'contentId': 2,
          'createdAt': now - 7200000, // 2小时前
        });
        await db.insert('review', {
          'content': 'Self-reflection is indeed crucial for personal growth. Great reminder!',
          'userId': 4,
          'contentId': 2,
          'createdAt': now - 3600000, // 1小时前
        });

        // 旅行主题内容的评论 (contentId: 4)
        await db.insert('review', {
          'content': 'Traveling has changed my perspective on life completely. Couldn\'t agree more!',
          'userId': 3,
          'contentId': 4,
          'createdAt': now - 5400000, // 1.5小时前
        });
        await db.insert('review', {
          'content': 'Meeting people from different cultures is the best part of traveling for me.',
          'userId': 5,
          'contentId': 4,
          'createdAt': now - 2700000, // 45分钟前
        });
        await db.insert('review', {
          'content': 'Adventure awaits! This post makes me want to book my next trip right now.',
          'userId': 1,
          'contentId': 4,
          'createdAt': now - 1200000, // 20分钟前
        });

        // 心理学习主题内容的评论 (contentId: 6)
        await db.insert('review', {
          'content': 'Understanding emotions is so important for mental health. Thank you for this insight.',
          'userId': 2,
          'contentId': 6,
          'createdAt': now - 4800000, // 1小时20分钟前
        });
        await db.insert('review', {
          'content': 'I\'ve been working on emotional regulation myself. This is very helpful!',
          'userId': 4,
          'contentId': 6,
          'createdAt': now - 2400000, // 40分钟前
        });

        // 早起主题内容的评论 (contentId: 9)
        await db.insert('review', {
          'content': 'Early rising has transformed my productivity! Totally agree with this.',
          'userId': 3,
          'contentId': 9,
          'createdAt': now - 6000000, // 1小时40分钟前
        });
        await db.insert('review', {
          'content': 'Morning routines are game-changers. Thanks for the motivation!',
          'userId': 5,
          'contentId': 9,
          'createdAt': now - 3000000, // 50分钟前
        });
        await db.insert('review', {
          'content': 'I struggle with waking up early, but this post inspires me to try again.',
          'userId': 2,
          'contentId': 9,
          'createdAt': now - 600000, // 10分钟前
        });

        // 插入默认收藏数据
        final favoriteTime = DateTime.now().millisecondsSinceEpoch;

        // 用户1 (Alpha) 的收藏 - 收藏一些哲学和心理学习内容
        await db.insert('favorite', {
          'userId': 1,
          'contentId': 2, // 哲学内容2 (Socrates quote)
          'createdAt': favoriteTime - 86400000 * 3, // 3天前
        });
        await db.insert('favorite', {
          'userId': 1,
          'contentId': 6, // 心理学习内容2 (Mindfulness)
          'createdAt': favoriteTime - 86400000 * 2, // 2天前
        });
        await db.insert('favorite', {
          'userId': 1,
          'contentId': 4, // 旅行内容1 (Exploring cultures)
          'createdAt': favoriteTime - 86400000 * 1, // 1天前
        });

        // 用户2 (Bravo) 的收藏 - 收藏旅行和早起内容
        await db.insert('favorite', {
          'userId': 2,
          'contentId': 5, // 旅行内容2 (Adventure)
          'createdAt': favoriteTime - 86400000 * 4, // 4天前
        });
        await db.insert('favorite', {
          'userId': 2,
          'contentId': 9, // 早起内容2 (Morning routines)
          'createdAt': favoriteTime - 86400000 * 2, // 2天前
        });
        await db.insert('favorite', {
          'userId': 2,
          'contentId': 1, // 哲学内容1 (Philosophy study)
          'createdAt': favoriteTime - 3600000 * 12, // 12小时前
        });

        // 用户3 (Charlie) 的收藏 - 收藏心理学习和哲学内容
        await db.insert('favorite', {
          'userId': 3,
          'contentId': 3, // 哲学内容3 (Existentialism)
          'createdAt': favoriteTime - 86400000 * 5, // 5天前
        });
        await db.insert('favorite', {
          'userId': 3,
          'contentId': 7, // 心理学习内容3 (CBT techniques)
          'createdAt': favoriteTime - 86400000 * 3, // 3天前
        });
        await db.insert('favorite', {
          'userId': 3,
          'contentId': 11, // 早起内容4 (Sleep schedule)
          'createdAt': favoriteTime - 3600000 * 6, // 6小时前
        });

        // 用户4 (Delta) 的收藏 - 收藏早起和旅行内容
        await db.insert('favorite', {
          'userId': 4,
          'contentId': 8, // 早起内容1 (Early to bed)
          'createdAt': favoriteTime - 86400000 * 6, // 6天前
        });
        await db.insert('favorite', {
          'userId': 4,
          'contentId': 4, // 旅行内容1 (Exploring cultures)
          'createdAt': favoriteTime - 86400000 * 1, // 1天前
        });

        // 用户5 (Echo) 的收藏 - 收藏心理学习和哲学内容
        await db.insert('favorite', {
          'userId': 5,
          'contentId': 2, // 哲学内容2 (Socrates quote)
          'createdAt': favoriteTime - 86400000 * 4, // 4天前
        });
        await db.insert('favorite', {
          'userId': 5,
          'contentId': 6, // 心理学习内容2 (Mindfulness)
          'createdAt': favoriteTime - 86400000 * 1, // 1天前
        });
        await db.insert('favorite', {
          'userId': 5,
          'contentId': 10, // 早起内容3 (Quiet hours)
          'createdAt': favoriteTime - 3600000 * 8, // 8小时前
        });

        // 插入默认聊天记录
        final chatTime = DateTime.now().millisecondsSinceEpoch;

        // Alpha (user1) 和 Bravo (user2) 的聊天
        await db.insert('chat', {
          'sendUserId': 1,
          'acceptUserId': 2,
          'context': 'Hey Bravo! How are you doing?',
          'sendTime': chatTime - 3600000 * 2, // 2小时前
        });
        await db.insert('chat', {
          'sendUserId': 2,
          'acceptUserId': 1,
          'context': 'Hi Alpha! I\'m doing great, thanks for asking!',
          'sendTime': chatTime - 3600000 * 2 + 300000, // 2小时前+5分钟
        });
        await db.insert('chat', {
          'sendUserId': 1,
          'acceptUserId': 2,
          'context': 'That\'s wonderful to hear! What have you been up to lately?',
          'sendTime': chatTime - 3600000 * 2 + 600000, // 2小时前+10分钟
        });
        await db.insert('chat', {
          'sendUserId': 2,
          'acceptUserId': 1,
          'context': 'I\'ve been exploring some new philosophical ideas. Really fascinating stuff!',
          'sendTime': chatTime - 3600000 * 1, // 1小时前
        });

        // Alpha (user1) 和 Charlie (user3) 的聊天
        await db.insert('chat', {
          'sendUserId': 3,
          'acceptUserId': 1,
          'context': 'Alpha, I saw your post about mindfulness. Really insightful!',
          'sendTime': chatTime - 1800000, // 30分钟前
        });
        await db.insert('chat', {
          'sendUserId': 1,
          'acceptUserId': 3,
          'context': 'Thank you Charlie! I\'m glad you found it helpful.',
          'sendTime': chatTime - 1200000, // 20分钟前
        });
        await db.insert('chat', {
          'sendUserId': 3,
          'acceptUserId': 1,
          'context': 'Would love to discuss more about it sometime!',
          'sendTime': chatTime - 600000, // 10分钟前
        });

        // Bravo (user2) 和 Delta (user4) 的聊天
        await db.insert('chat', {
          'sendUserId': 2,
          'acceptUserId': 4,
          'context': 'Delta, how\'s your early morning routine going?',
          'sendTime': chatTime - 86400000, // 1天前
        });
        await db.insert('chat', {
          'sendUserId': 4,
          'acceptUserId': 2,
          'context': 'It\'s been amazing! I feel so much more productive.',
          'sendTime': chatTime - 86400000 + 1800000, // 1天前+30分钟
        });

        // Charlie (user3) 和 Echo (user5) 的聊天
        await db.insert('chat', {
          'sendUserId': 5,
          'acceptUserId': 3,
          'context': 'Charlie, I loved your perspective on staying strong!',
          'sendTime': chatTime - 300000, // 5分钟前
        });
        await db.insert('chat', {
          'sendUserId': 3,
          'acceptUserId': 5,
          'context': 'Thanks Echo! We all need encouragement sometimes.',
          'sendTime': chatTime - 120000, // 2分钟前
        });

        // 插入默认好友关系
        final friendshipTime = DateTime.now().millisecondsSinceEpoch;

        // Alpha (user1) 和 Bravo (user2) 是好友
        await db.insert('friendship', {
          'userId': 1,
          'friendId': 2,
          'createdAt': friendshipTime - 86400000 * 7, // 7天前
        });

        // Alpha (user1) 和 Charlie (user3) 是好友
        await db.insert('friendship', {
          'userId': 1,
          'friendId': 3,
          'createdAt': friendshipTime - 86400000 * 5, // 5天前
        });

        // Bravo (user2) 和 Delta (user4) 是好友
        await db.insert('friendship', {
          'userId': 2,
          'friendId': 4,
          'createdAt': friendshipTime - 86400000 * 3, // 3天前
        });

        // Charlie (user3) 和 Echo (user5) 是好友
        await db.insert('friendship', {
          'userId': 3,
          'friendId': 5,
          'createdAt': friendshipTime - 86400000 * 2, // 2天前
        });

        // Delta (user4) 和 Echo (user5) 是好友
        await db.insert('friendship', {
          'userId': 4,
          'friendId': 5,
          'createdAt': friendshipTime - 86400000 * 1, // 1天前
        });

        // 插入默认计划
        final currentTime = DateTime.now();
        final today = DateTime(currentTime.year, currentTime.month, currentTime.day);

        // Alpha (user1) 的计划
        // 今天未完成的计划
        await db.insert('plan', {
          'title': 'Morning Exercise',
          'description': 'Start the day with 30 minutes of cardio and stretching',
          'picture': AwakImages.plan1,
          'startTime': today.add(Duration(hours: 6)).millisecondsSinceEpoch,
          'endTime': today.add(Duration(hours: 7)).millisecondsSinceEpoch,
          'state': 'uncompleted',
          'userId': 1,
        });

        await db.insert('plan', {
          'title': 'Read Philosophy Book',
          'description': 'Continue reading "Meditations" by Marcus Aurelius',
          'picture': AwakImages.plan2,
          'startTime': today.add(Duration(hours: 19)).millisecondsSinceEpoch,
          'endTime': today.add(Duration(hours: 21)).millisecondsSinceEpoch,
          'state': 'uncompleted',
          'userId': 1,
        });

        await db.insert('plan', {
          'title': 'Meditation',
          'description': 'Daily mindfulness meditation for mental clarity',
          'picture': AwakImages.plan6,
          'startTime': today.add(Duration(hours: 22, minutes: 30)).millisecondsSinceEpoch,
          'endTime': today.add(Duration(hours: 23, minutes: 0)).millisecondsSinceEpoch,
          'state': 'uncompleted',
          'userId': 1,
        });

        // 本周已完成的计划
        await db.insert('plan', {
          'title': 'Modeling Practice',
          'description': 'Practice 3D modeling techniques and create new designs',
          'picture': AwakImages.plan3,
          'startTime': today.subtract(Duration(days: 2)).add(Duration(hours: 14)).millisecondsSinceEpoch,
          'endTime': today.subtract(Duration(days: 2)).add(Duration(hours: 16)).millisecondsSinceEpoch,
          'state': 'completed',
          'userId': 1,
        });

        await db.insert('plan', {
          'title': 'Yoga Session',
          'description': 'Morning yoga routine for flexibility and mindfulness',
          'picture': AwakImages.plan4,
          'startTime': today.subtract(Duration(days: 1)).add(Duration(hours: 7)).millisecondsSinceEpoch,
          'endTime': today.subtract(Duration(days: 1)).add(Duration(hours: 8)).millisecondsSinceEpoch,
          'state': 'completed',
          'userId': 1,
        });

        // 本周未完成的计划
        await db.insert('plan', {
          'title': 'Learn New Language',
          'description': 'Study Spanish vocabulary and practice conversation',
          'picture': AwakImages.plan5,
          'startTime': today.add(Duration(days: 1)).add(Duration(hours: 20)).millisecondsSinceEpoch,
          'endTime': today.add(Duration(days: 1)).add(Duration(hours: 22)).millisecondsSinceEpoch,
          'state': 'uncompleted',
          'userId': 1,
        });

        await db.insert('plan', {
          'title': 'Meditation',
          'description': 'Daily mindfulness meditation for mental clarity',
          'picture': AwakImages.plan6,
          'startTime': today.add(Duration(days: 2)).add(Duration(hours: 6)).millisecondsSinceEpoch,
          'endTime': today.add(Duration(days: 2)).add(Duration(hours: 6, minutes: 30)).millisecondsSinceEpoch,
          'state': 'uncompleted',
          'userId': 1,
        });

        // Bravo (user2) 的计划
        await db.insert('plan', {
          'title': 'Cooking Class',
          'description': 'Learn to cook Italian cuisine with professional chef',
          'picture': AwakImages.plan7,
          'startTime': today.add(Duration(hours: 18)).millisecondsSinceEpoch,
          'endTime': today.add(Duration(hours: 20)).millisecondsSinceEpoch,
          'state': 'uncompleted',
          'userId': 2,
        });

        await db.insert('plan', {
          'title': 'Art Workshop',
          'description': 'Watercolor painting techniques and landscape art',
          'picture': AwakImages.plan1,
          'startTime': today.subtract(Duration(days: 3)).add(Duration(hours: 15)).millisecondsSinceEpoch,
          'endTime': today.subtract(Duration(days: 3)).add(Duration(hours: 17)).millisecondsSinceEpoch,
          'state': 'completed',
          'userId': 2,
        });

        // Charlie (user3) 的计划
        await db.insert('plan', {
          'title': 'Music Practice',
          'description': 'Piano practice and composition work',
          'picture': AwakImages.plan2,
          'startTime': today.add(Duration(hours: 16)).millisecondsSinceEpoch,
          'endTime': today.add(Duration(hours: 18)).millisecondsSinceEpoch,
          'state': 'uncompleted',
          'userId': 3,
        });

        await db.insert('plan', {
          'title': 'Photography Walk',
          'description': 'Capture urban landscapes and street photography',
          'picture': AwakImages.plan3,
          'startTime': today.subtract(Duration(days: 1)).add(Duration(hours: 10)).millisecondsSinceEpoch,
          'endTime': today.subtract(Duration(days: 1)).add(Duration(hours: 12)).millisecondsSinceEpoch,
          'state': 'completed',
          'userId': 3,
        });

        // 为user1添加过去30天的历史计划
        await db.insert('plan', {
          'title': 'Grocery Shopping',
          'description': 'Buy fresh vegetables and organic products',
          'picture': AwakImages.plan4,
          'startTime': today.subtract(Duration(days: 5)).add(Duration(hours: 10)).millisecondsSinceEpoch,
          'endTime': today.subtract(Duration(days: 5)).add(Duration(hours: 11)).millisecondsSinceEpoch,
          'state': 'completed',
          'userId': 1,
        });

        await db.insert('plan', {
          'title': 'Team Meeting',
          'description': 'Weekly project review and planning session',
          'picture': AwakImages.plan5,
          'startTime': today.subtract(Duration(days: 7)).add(Duration(hours: 14)).millisecondsSinceEpoch,
          'endTime': today.subtract(Duration(days: 7)).add(Duration(hours: 16)).millisecondsSinceEpoch,
          'state': 'completed',
          'userId': 1,
        });

        await db.insert('plan', {
          'title': 'Gym Workout',
          'description': 'Strength training and cardio session',
          'picture': AwakImages.plan6,
          'startTime': today.subtract(Duration(days: 10)).add(Duration(hours: 18)).millisecondsSinceEpoch,
          'endTime': today.subtract(Duration(days: 10)).add(Duration(hours: 20)).millisecondsSinceEpoch,
          'state': 'failed',
          'userId': 1,
        });

        await db.insert('plan', {
          'title': 'Book Club Meeting',
          'description': 'Discuss "The Art of War" by Sun Tzu',
          'picture': AwakImages.plan7,
          'startTime': today.subtract(Duration(days: 15)).add(Duration(hours: 19)).millisecondsSinceEpoch,
          'endTime': today.subtract(Duration(days: 15)).add(Duration(hours: 21)).millisecondsSinceEpoch,
          'state': 'completed',
          'userId': 1,
        });

        await db.insert('plan', {
          'title': 'Hiking',
          'description': 'Explore mountain trails and enjoy nature',
          'picture': AwakImages.plan1,
          'startTime': today.subtract(Duration(days: 20)).add(Duration(hours: 8)).millisecondsSinceEpoch,
          'endTime': today.subtract(Duration(days: 20)).add(Duration(hours: 16)).millisecondsSinceEpoch,
          'state': 'completed',
          'userId': 1,
        });

        // 插入默认今日目标
        await db.insert('today_goal', {
          'name': 'Morning Exercise',
          'userId': 1,
        });
        await db.insert('today_goal', {
          'name': 'Read Philosophy Book',
          'userId': 1,
        });
        await db.insert('today_goal', {
          'name': 'Learn New Language',
          'userId': 1,
        });
        await db.insert('today_goal', {
          'name': 'Healthy Eating',
          'userId': 1,
        });

        // 为其他用户添加一些今日目标
        await db.insert('today_goal', {
          'name': 'Cooking Practice',
          'userId': 2,
        });
        await db.insert('today_goal', {
          'name': 'Art Workshop',
          'userId': 2,
        });
        await db.insert('today_goal', {
          'name': 'Music Practice',
          'userId': 3,
        });
        await db.insert('today_goal', {
          'name': 'Photography',
          'userId': 3,
        });
      },
      onUpgrade: (db, oldVersion, newVersion) async {
        if (oldVersion < 2) {
          // 添加topicId列到content表
          await db.execute('ALTER TABLE content ADD COLUMN topicId INTEGER');
          // 为现有内容设置默认topicId (如果有的话)
          await db.execute('UPDATE content SET topicId = 1 WHERE topicId IS NULL');
        }
        if (oldVersion < 3) {
          // 创建review表
          await db.execute('''
            CREATE TABLE review (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              content TEXT NOT NULL,
              userId INTEGER NOT NULL,
              contentId INTEGER NOT NULL,
              createdAt INTEGER,
              FOREIGN KEY (userId) REFERENCES user (id),
              FOREIGN KEY (contentId) REFERENCES content (id)
            )
          ''');
        }
        if (oldVersion < 4) {
          // 添加publishedAt列到content表
          await db.execute('ALTER TABLE content ADD COLUMN publishedAt INTEGER');
          // 为现有内容设置默认发布时间
          final now = DateTime.now().millisecondsSinceEpoch;
          await db.execute('UPDATE content SET publishedAt = ? WHERE publishedAt IS NULL', [now]);
        }
        if (oldVersion < 5) {
          // 创建favorite表
          await db.execute('''
            CREATE TABLE favorite (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              userId INTEGER NOT NULL,
              contentId INTEGER NOT NULL,
              createdAt INTEGER,
              FOREIGN KEY (userId) REFERENCES user (id),
              FOREIGN KEY (contentId) REFERENCES content (id),
              UNIQUE(userId, contentId)
            )
          ''');
        }
        if (oldVersion < 6) {
          // 创建chat表
          await db.execute('''
            CREATE TABLE chat (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              sendUserId INTEGER NOT NULL,
              acceptUserId INTEGER NOT NULL,
              context TEXT NOT NULL,
              sendTime INTEGER NOT NULL,
              FOREIGN KEY (sendUserId) REFERENCES user (id),
              FOREIGN KEY (acceptUserId) REFERENCES user (id)
            )
          ''');
        }
        if (oldVersion < 7) {
          // 创建friendship表
          await db.execute('''
            CREATE TABLE friendship (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              userId INTEGER NOT NULL,
              friendId INTEGER NOT NULL,
              createdAt INTEGER NOT NULL,
              FOREIGN KEY (userId) REFERENCES user (id),
              FOREIGN KEY (friendId) REFERENCES user (id),
              UNIQUE(userId, friendId)
            )
          ''');
        }
        if (oldVersion < 8) {
          // 为chat表添加isRead字段
          await db.execute('ALTER TABLE chat ADD COLUMN isRead INTEGER DEFAULT 0');
        }
        if (oldVersion < 9) {
          // 创建plan表
          await db.execute('''
            CREATE TABLE plan (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              title TEXT NOT NULL,
              picture TEXT NOT NULL,
              startTime INTEGER NOT NULL,
              endTime INTEGER NOT NULL,
              state TEXT NOT NULL,
              userId INTEGER NOT NULL,
              FOREIGN KEY (userId) REFERENCES user (id)
            )
          ''');
        }
        if (oldVersion < 10) {
          // 为plan表添加description字段
          await db.execute('ALTER TABLE plan ADD COLUMN description TEXT DEFAULT \'\'');
        }
        if (oldVersion < 11) {
          // 创建今日目标表
          await db.execute('''
            CREATE TABLE today_goal (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              name TEXT NOT NULL,
              userId INTEGER NOT NULL,
              FOREIGN KEY (userId) REFERENCES user (id)
            )
          ''');
        }
      },
    );
  }
}